#!/usr/bin/env python
# -*- encoding: utf-8 -*-

import pandas as pd
import numpy as np
import datetime
from xpy3lib.XRetryableQuery import XRetryableQuery
from xpy3lib.XRetryableSave import XRetryableSave
from sicost.AbstractDPJob import AbstractDPJob


class BZCBJSJob(AbstractDPJob):
    """
    通过两个SQL去取来源表和目标表的表结构

    传入6个参数   来源标  和目标表  及开始时间结束时间

    将来源表那段时间的数据取出来  在存到目标表里
    """
    data_type = None
    def __init__(self,
                 p_config=None,
                 p_db_conn_mpp=None,
                 p_db_conn_rds=None,
                 p_db_conn_dbprod7=None,
                 p_unit=None,
                 p_account=None,
                 p_cost_center=None,
                 p_account_period_start=None,
                 p_account_period_end=None,
                 p_data_type=None):


        super(BZCBJSJob, self).__init__(p_config=p_config,
                                        p_db_conn_mpp=p_db_conn_mpp,
                                        p_db_conn_rds=p_db_conn_rds,
                                        p_db_conn_dbprod7=p_db_conn_dbprod7,
                                        p_unit=p_unit,
                                        p_account=p_account,
                                        p_cost_center=p_cost_center,
                                        p_account_period_start=p_account_period_start,
                                        p_account_period_end=p_account_period_end)
        self.data_type = p_data_type
        pass

    def do_execute(self):
        """
        先删除历史数据
        """
        self.logger.info('BZCBJSJob.do_execute')
        sql = " DELETE FROM BGTARAS1.T_ADS_FACT_SICB_CA0001 " \
              " WHERE LEFT(PRODUCE_TIME,14) BETWEEN '%s' AND '%s' " \
              " AND ACCT = '%s' " \
              " AND COST_CENTER = '%s' " \
              " AND UNIT_CODE = '%s' " \
              " AND DATA_TYPE = '%s' " \
              " AND COST_SUBJECT = '65000' "%(self.account_period_start,self.account_period_end,self.account,self.cost_center,self.unit,self.data_type)



        self.logger.info(sql)
        # self.db_conn_rds.execute(sql)





        # sql = " SELECT DISTINCT MAT_NO, PACK_TYPE_CODE " \
        #       " FROM BGRAGGCB.TCBPKJ404 " \
        #       " WHERE   MAT_NO IN (SELECT MAT_NO FROM BGRAGGCB.V_TACAIIQ WHERE COST_CENTER = 'P_COST_CENTER' " \
        #       " AND APP_THROW_AI_DATE||APP_THROW_AI_T BETWEEN 'P_ACCOUNT_PERIOD_START' AND 'P_ACCOUNT_PERIOD_END')" \
        #       " AND (MAT_NO, PROD_START_TIME) IN " \
        #       " (SELECT MAT_NO,MAX (PROD_START_TIME) AS PROD_START_TIME " \
        #       " FROM BGRAGGCB.TCBPKJ404 " \
        #       " WHERE    MAT_NO IN (SELECT MAT_NO FROM BGRAGGCB.V_TACAIIQ WHERE COST_CENTER = 'P_COST_CENTER'  " \
        #       " AND APP_THROW_AI_DATE||APP_THROW_AI_T BETWEEN 'P_ACCOUNT_PERIOD_START' AND 'P_ACCOUNT_PERIOD_END')" \
        #       " GROUP BY  MAT_NO) "
        # self.logger.info(sql)
        #
        # df_BZCB0101 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        # success = df_BZCB0101.empty is False
        # if success is False:
        #     return
        # df_BZCB0101.columns = df_BZCB0101.columns.str.upper()
        #
        #
        # df_BZCB0101.rename(columns={'ACCOUNT': 'ACCT'}, inplace=True)



        # sql = " SELECT " \
        #       " MAT_NO " \
        #       " FROM " \
        #       " TMSIJ4.TTMSIJ495 " \
        #       " WHERE 1=1 " \
        #       " AND COST_CENTER = '%s'  " \
        #       " AND APP_THROW_AI_DATE||APP_THROW_AI_T BETWEEN '%s' " \
        #       " AND '%s'"%(self.cost_center,self.account_period_start,self.account_period_end)
        # self.logger.info(sql)
        #
        # df1 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        # success = df1.empty is False
        # if success is False:
        #     return
        # df1.columns = df1.columns.str.upper()
        #
        # sql = " SELECT " \
        #       " MAT_NO " \
        #       " FROM " \
        #       " BGRAGGCB.TACAIIQB " \
        #       " WHERE 1=1 " \
        #       " AND COST_CENTER = '%s'  " \
        #       " AND APP_THROW_AI_DATE||APP_THROW_AI_T BETWEEN '%s' " \
        #       " AND '%s'"%(self.cost_center,self.account_period_start,self.account_period_end)
        #
        # self.logger.info(sql)
        #
        # df2 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        # success = df2.empty is False
        # if success is False:
        #     return
        # df2.columns = df2.columns.str.upper()
        #
        # df_V_TACAIIQ = pd.concat([df1, df2], ignore_index=True)
        #
        # df_V_TACAIIQ['MAT_NO'] = df_V_TACAIIQ['MAT_NO'].astype(str)
        # tmp_l = df_V_TACAIIQ['MAT_NO'].values.tolist()
        # r = "'%s'" % ("','".join(tmp_l))
        #
        #
        # sql = " SELECT " \
        #       " MAT_NO ," \
        #       " PROD_START_TIME " \
        #       " FROM " \
        #       " BGRAGGCB.TCBPKJ404 " \
        #       " WHERE 1=1" \
        #       " AND MAT_NO IN (%s) GROUP BY MAT_NO" % (r)
        # self.logger.info(sql)
        #
        # df3 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        # success = df3.empty is False
        # if success is False:
        #     return
        # df3.columns = df3.columns.str.upper()
        # v = ['MAT_NO']
        # df4 = pd.merge(df_V_TACAIIQ, df3, on=v, how='left')
        # df4.rename(columns={'PROD_START_TIME': 'PROD_START_TIME_OLD'}, inplace=True)
        #
        # df4['PROD_START_TIME'] = df4.groupby(v)['PROD_START_TIME_OLD'].rank(ascending=0, method='dense')
        # df5 = df4
        #
        # sql = " SELECT " \
        #       " MAT_NO, " \
        #       " PACK_TYPE_CODE " \
        #       " FROM " \
        #       " BGRAGGCB.TCBPKJ404 "
        # self.logger.info(sql)
        #
        # df6 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        # success = df6.empty is False
        # if success is False:
        #     return
        # df6.columns = df6.columns.str.upper()
        #
        # v = ['MAT_NO','PROD_START_TIME']
        # df7 = pd.merge(df5, df6, on=v, how='left')
        # v = ['MAT_NO', 'PACK_TYPE_CODE']
        # df7.drop_duplicates(subset=v, keep='first', inplace=True)
        # df_BZCB0101 = df7
        sql = " SELECT " \
              " A.ACCOUNT," \
              " LEFT(A.COST_CENTER,2) AS FACTORY," \
              " A.COST_CENTER," \
              " '' AS UNIT," \
              " A.APP_THROW_AI_DATE||A.APP_THROW_AI_T AS WORK_TIME," \
              " A.APP_THROW_AI_DATE||A.APP_THROW_AI_T AS PROCESS_START_TIME," \
              " A.APP_THROW_AI_DATE||A.APP_THROW_AI_T AS PROCESS_END_TIME," \
              " A.PRODUCT_CODE, " \
              " A.ST_NO," \
              " A.MAT_ACT_WIDTH," \
              " '' AS WIDTH," \
              " '' AS WIDTH_COD," \
              " A.MAT_ACT_THICK," \
              " '' AS THICK," \
              " '' AS THICK_COD," \
              " A.MAT_NO," \
              " '' AS ITEM_ID," \
              " '' AS JHZ_KEY," \
              " '' AS JHZ_KEY_NAME," \
              " A.DEVO_PRODUCT_CODE AS IN_PRODUCT_CODE," \
              " A.IN_MAT_NO," \
              " A.MAT_ACT_WT AS WT," \
              " A.MAT_ACT_WT AS ACT_WT," \
              " A.IN_MAT_WT_AI AS IN_WT," \
              " A.IN_MAT_WT_AI AS ACT_IN_WT,   " \
              " '65000' AS WCE," \
              " '' AS WCE_NAME," \
              " '' AS WCE_UNIT," \
              " 1.00 AS PRICE," \
              " 0 AS B_ACT_AMT," \
              " A.APP_THROW_AI_MODE," \
              " A.PACK_TYPE_CODE AS LAYER_TYPE," \
              " 0 AS MAT_ACT_LEN," \
              " 0 AS MAT_ACT_AREA," \
              " A.TOP_COAT_WT," \
              " A.BOT_COAT_WT," \
              " '%s' AS DATA_TYPE," \
              " '' AS TYPE," \
              " CASE WHEN COST_CENTER IN ('MIDN', " \
              " 'MIDM','MIDY','MIDZ') " \
              " THEN '无取向硅钢' " \
              " WHEN COST_CENTER IN ('MICX'," \
              " 'MIHY','MIHZ','MICZ') " \
              " THEN '取向硅钢' END AS QS_NAME, " \
              " A.MAT_ACT_WT as A_MAT_ACT_WT, " \
              " A.PACK_TYPE_CODE " \
              " FROM TMSIJ4.TTMSIJ495 A " \
              " WHERE A.ACCOUNT = '%s' " \
              " AND A.COST_CENTER = '%s' " \
              " AND A.APP_THROW_AI_DATE||A.APP_THROW_AI_T BETWEEN '%s' AND '%s' " \
              " AND NVL(A.PACK_TYPE_CODE,'') <> '' " \
              " AND A.MAT_ACT_WT != 0 " \
              " AND A.ACCOUNT_TITLE_ITEM='01' " % (
              self.data_type, self.account, self.cost_center, self.account_period_start, self.account_period_end)

        self.logger.info(sql)
        # sql = " SELECT " \
        #       " a.*," \
        #       " CASE WHEN COST_CENTER IN ('MIDN', " \
        #       " 'MIDM','MIDY','MIDZ') " \
        #       " THEN '无取向硅钢' " \
        #       " WHEN COST_CENTER IN ('MICX'," \
        #       " 'MIHY','MIHZ','MICZ') " \
        #       " THEN '取向硅钢' END AS QS_NAME, " \
        #       " APP_THROW_AI_DATE||APP_THROW_AI_T AS WORK_TIME " \
        #       " FROM TMSIJ4.TTMSIJ495 a " \
        #       " WHERE ACCOUNT = '%s' " \
        #       " AND COST_CENTER = '%s' " \
        #       " AND APP_THROW_AI_DATE||APP_THROW_AI_T BETWEEN '%s' AND '%s' " \
        #       " AND NVL(A.PACK_TYPE_CODE,'') <> '' " \
        #       " AND A.MAT_ACT_WT != 0 " \
        #       " AND A.ACCOUNT_TITLE_ITEM='01' "%(self.account,self.cost_center,self.account_period_start,self.account_period_end)
        # self.logger.info(sql)

        df8 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        # success = df8.empty is False
        # if success is False:
        #     return
        # df8.columns = df8.columns.str.upper()

        sql = " SELECT " \
              " A.ACCOUNT," \
              " LEFT(A.COST_CENTER,2) AS FACTORY," \
              " A.COST_CENTER," \
              " '' AS UNIT," \
              " A.APP_THROW_AI_DATE||A.APP_THROW_AI_T AS WORK_TIME," \
              " A.APP_THROW_AI_DATE||A.APP_THROW_AI_T AS PROCESS_START_TIME," \
              " A.APP_THROW_AI_DATE||A.APP_THROW_AI_T AS PROCESS_END_TIME," \
              " A.PRODUCT_CODE, " \
              " A.ST_NO," \
              " A.MAT_ACT_WIDTH," \
              " '' AS WIDTH," \
              " '' AS WIDTH_COD," \
              " A.MAT_ACT_THICK," \
              " '' AS THICK," \
              " '' AS THICK_COD," \
              " A.MAT_NO," \
              " '' AS ITEM_ID," \
              " '' AS JHZ_KEY," \
              " '' AS JHZ_KEY_NAME," \
              " A.DEVO_PRODUCT_CODE AS IN_PRODUCT_CODE," \
              " A.IN_MAT_NO," \
              " A.MAT_ACT_WT AS WT," \
              " A.MAT_ACT_WT AS ACT_WT," \
              " A.IN_MAT_WT_AI AS IN_WT," \
              " A.IN_MAT_WT_AI AS ACT_IN_WT,   " \
              " '65000' AS WCE," \
              " '' AS WCE_NAME," \
              " '' AS WCE_UNIT," \
              " 1.00 AS PRICE," \
              " 0 AS B_ACT_AMT," \
              " A.APP_THROW_AI_MODE," \
              " A.PACK_TYPE_CODE AS LAYER_TYPE," \
              " 0 AS MAT_ACT_LEN," \
              " 0 AS MAT_ACT_AREA," \
              " A.TOP_COAT_WT," \
              " A.BOT_COAT_WT," \
              " '%s' AS DATA_TYPE," \
              " '' AS TYPE," \
              " CASE WHEN COST_CENTER IN ('MIDN', " \
              " 'MIDM','MIDY','MIDZ') " \
              " THEN '无取向硅钢' " \
              " WHEN COST_CENTER IN ('MICX'," \
              " 'MIHY','MIHZ','MICZ') " \
              " THEN '取向硅钢' END AS QS_NAME, " \
              " A.MAT_ACT_WT as A_MAT_ACT_WT, " \
              " A.PACK_TYPE_CODE, " \
              " 'BGRAGGCB' AS REC_REVISOR," \
              " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME," \
              " 'BGRAGGCB' AS REC_CREATOR," \
              " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME" \
              " FROM BGRAGGCB.TACAIIQB A " \
              " WHERE A.ACCOUNT = '%s' " \
              " AND A.COST_CENTER = '%s' " \
              " AND A.APP_THROW_AI_DATE||A.APP_THROW_AI_T BETWEEN '%s' AND '%s' " \
              " AND NVL(A.PACK_TYPE_CODE,'') <> '' " \
              " AND A.MAT_ACT_WT != 0 " \
              " AND A.ACCOUNT_TITLE_ITEM='01' "%(self.data_type,self.account,self.cost_center,self.account_period_start,self.account_period_end)

        self.logger.info(sql)

        df9 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        # success = df9.empty is False
        # if success is False:
        #     return
        # df9.columns = df9.columns.str.upper()

        df_BZCB0102 = pd.concat([df8, df9], ignore_index=True)
        success = df_BZCB0102.empty is False
        if success is False:
            return
        df_BZCB0102.columns = df_BZCB0102.columns.str.upper()
        print(df_BZCB0102)
        # sql = " DELETE FROM BGRAGGCB.SU_AJBG_CA_BZ " \
        #       " WHERE DATA_TYPE = 'P_TYPE' " \
        #       " AND MAT_NO IN (SELECT MAT_NO FROM '||v_schema||'.BZCB0102_'||P_COST_CENTER||') "
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        # sql = " SELECT A.MAT_NO,A.PACK_TYPE_CODE,A.PROD_CLASS_CODE,A.PACK_MODE_CODE,A.MAT_ACT_INNER_DIA,A.MAT_ACT_OUTER_DIA,A.MAT_THICK,A.MAT_WIDTH, " \
        #       " A.MAT_WT,A.SG_SIGN,A.PROD_START_TIME,A.PROD_END_TIME,A.MAT_CODE,A.MAT_NAME," \
        #       " A.DEVO_WT,A.DEVO_WT,'P_TYPE' AS DATA_TYPE" \
        #       " FROM BGRAGGCB.TCBPKJ404 A " \
        #       " BGRAGGCB.WH_AJBG_WLRPICE "
        # self.logger.info(sql)
        #
        # df11 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        # success = df11.empty is False
        # if success is False:
        #     return
        # df11.columns = df11.columns.str.upper()
        # sql = " SELECT ITEM_ID as MAT_CODE,AVG(UNIT_PRICE) AS UNIT_PRICE " \
        #       " FROM BGRAGGCB.WH_AJBG_WLRPICE " \
        #       " WHERE (YEAR_MON,ITEM_ID) IN (SELECT MAX(YEAR_MON) AS YEAR_MON,ITEM_ID FROM BGRAGGCB.WH_AJBG_WLRPICE GROUP BY ITEM_ID)" \
        #       " GROUP BY ITEM_ID )B "
        # self.logger.info(sql)
        #
        # df10 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        # success = df10.empty is False
        # if success is False:
        #     return
        # df10.columns = df10.columns.str.upper()
        # v = ['MAT_NO']
        # df12 = pd.merge(df_BZCB0102, df10, on=v, how='left')
        # v = ['MAT_CODE']
        # df_CA_BZ = pd.merge(df12, df11, on=v, how='left')
        # df_CA_BZ['ACT_COSTING'] = df_CA_BZ['DEVO_WT']*df_CA_BZ['UNIT_PRICE']
        df_BZCB0102['MAT_NO'] = df_BZCB0102['MAT_NO'].astype(str)
        l = df_BZCB0102['MAT_NO'].values.tolist()
        r = "'%s'" % ("','".join(l))
        sql = " SELECT " \
              " MAT_NO,SUM(A.ACT_COSTING) AS C_CONSUME " \
              " FROM CBPKJ4.TCBPKJ404 A " \
              " WHERE 1=1" \
              " AND MAT_NO IN (%s) GROUP BY MAT_NO" % (r)
        self.logger.info(sql)

        df_BZCB0103 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        success = df_BZCB0103.empty is False
        if success is False:
            return
        df_BZCB0103.columns = df_BZCB0103.columns.str.upper()


        # df_BZCB0103 = df12.groupby(v)['ACT_COSTING'].agg([np.sum]).round(2)
        #
        # v = ['MAT_NO']
        # #df = pd.merge(df_BZCB0102 , df_BZCB0101, on=v, how='left')
        v = ['MAT_NO']
        df = pd.merge(df_BZCB0102, df_BZCB0103, on=v, how='left')
        sql = " SELECT " \
              " ACCT as ACCOUNT ," \
              " COST_CENTER ," \
              " PRODUCT_CODE ," \
              " LEFT(PACK_MODE_CODE,2) as PACK_TYPE_CODE ," \
              " UNIT_COST as D_AMOUNT_ACT " \
              " FROM " \
              " BGTARAS1.T_ADS_FACT_SICB_BZBZCB " \
              " WHERE 1=1 " \
              " AND COST_SUBJECT = '65000'"
        self.logger.info(sql)

        df_BZBZCB = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()
        success = df_BZBZCB.empty is False
        if success is False:
            return
        df_BZBZCB.columns = df_BZBZCB.columns.str.upper()
        v = ['ACCOUNT','COST_CENTER','PRODUCT_CODE','PACK_TYPE_CODE']
        df = pd.merge(df, df_BZBZCB, on=v, how='left')
        df['MONTH'] = df['WORK_TIME'].str[0:6]
        sql = " SELECT " \
              " MONTH,ACCT as ACCOUNT,PROD_CAT as QS_NAME,SUM (CASE WHEN FEE_TYPE = '资材费' THEN 0 ELSE UNIT_COST END) AS E_AMOUNT_ACT " \
              " FROM " \
              " BGTARAS1.T_ADS_FACT_SICB_BZBZFY " \
              " WHERE 1=1 " \
              " GROUP BY MONTH,ACCT,PROD_CAT"
        self.logger.info(sql)

        df_BZBZFY = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()
        success = df_BZBZFY.empty is False
        if success is False:
            return
        df_BZBZFY.columns = df_BZBZFY.columns.str.upper()
        v = ['MONTH','ACCOUNT','QS_NAME']
        df = pd.merge(df, df_BZBZFY, on=v, how='left')



        # 班次列名为team, 需要把1234 转换为甲乙丙丁 。
        # 班别列名为shift， 需要把12，要转成夜日。
        # def __cal_TEAM(x):
        # return self.__cal_TEAM(x)

        # df5_new['TEAM'] = df5_new.apply(lambda x: __cal_TEAM(x), axis=1)

        # def __cal_SHIFT(x):
        # return self.__cal_SHIFT(x)
        def __cal_FROM(x):
            # NOTE 距离最近的08:00或20:00
            t = datetime.datetime.strptime(str(x.WORK_TIME), '%Y%m%tmp_dict%H%M%S')
            prev_day = t - datetime.timedelta(days=1)
            today_20 = datetime.datetime(year=t.year, month=t.month, day=t.day, hour=20).strftime('%Y%m%tmp_dict%H%M%S')
            today_8 = datetime.datetime(year=t.year, month=t.month, day=t.day, hour=8).strftime('%Y%m%tmp_dict%H%M%S')
            yestoday_20 = datetime.datetime(year=prev_day.year, month=prev_day.month, day=prev_day.day,
                                            hour=20).strftime('%Y%m%tmp_dict%H%M%S')
            if x.WORK_TIME >= today_20:
                rst = today_20
            elif x.WORK_TIME >= today_8:
                rst = today_8
            else:
                rst = yestoday_20
            return rst

        df['FROM'] = df.apply(lambda x: __cal_FROM(x), axis=1)
        sql = "select FROM,TO,DATE,SHIFT,TURN AS TEAM from bgrasids.BASE_SU_J003"
        df_turn = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        df_turn.columns = df_turn.columns.str.upper()
        # self.generate_excel(p_dataframe=df6, p_file_name="df6.xls")
        df_new = pd.merge(df, df_turn, on=['FROM'], how='left')
        def __cal_TEAM(x):
            rst = {1: '甲', 2: '乙', 3: '丙', 4: '丁'}[x.TEAM]
            return rst

        def __cal_SHIFT(x):
            rst = {1: '夜', 2: '日'}[x.SHIFT]
            return rst

        df_new['TEAM'] = df_new.apply(lambda x: __cal_TEAM(x), axis=1)

        df_new['SHIFT'] = df_new.apply(lambda x: __cal_SHIFT(x), axis=1)
        df_new.drop(['FROM'], axis=1, inplace=True)
        df_new.drop(['TO'], axis=1, inplace=True)
        df_new.drop(['DATE'], axis=1, inplace=True)
        df_new['C_CONSUME'].fillna(value=0, inplace=True)
        df_new['E_AMOUNT_ACT'].fillna(value=0, inplace=True)
        df_new['D_AMOUNT_ACT'].fillna(value=0, inplace=True)


        def __cal_PN_MAT_ACT_WT(x):
            rst = 0
            if x.A_MAT_ACT_WT < 0:
                rst = -1.00
            else:
                rst = 1.00
            return rst
        df_new['PN'] = df_new.apply(lambda x: __cal_PN_MAT_ACT_WT(x), axis=1)

        def __cal_ABS_AE(x):
            rst = 0
            if x.A_MAT_ACT_WT * x.E_AMOUNT_ACT < 0:
                rst = -1.00 * x.A_MAT_ACT_WT * x.E_AMOUNT_ACT
            else:
                rst = x.A_MAT_ACT_WT * x.E_AMOUNT_ACT
            return rst

        df_new['ABS_AE'] = df_new.apply(lambda x: __cal_ABS_AE(x), axis=1)
        # df_new['ABS_AE'] = df_new['A_MAT_ACT_WT'] * df_new['E_AMOUNT_ACT']
        def __cal_ABS_CA(x):
            rst = 0
            if x.C_CONSUME / x.A_MAT_ACT_WT < 0:
                rst = -1.00 * x.C_CONSUME / x.A_MAT_ACT_WT
            else:
                rst = x.C_CONSUME / x.A_MAT_ACT_WT
            return rst

        df_new['ABS_CA'] = df_new.apply(lambda x: __cal_ABS_CA(x), axis=1)

        def __cal_ABS_A(x):
            rst = 0
            if x.A_MAT_ACT_WT < 0:
                rst = -1.00 * x.A_MAT_ACT_WT
            else:
                rst = x.A_MAT_ACT_WT
            return rst

        df_new['ABS_A'] = df_new.apply(lambda x: __cal_ABS_A(x), axis=1)

        df_new['ACT_N'] = df_new['PN'] * (df_new['ABS_AE'] + df_new['E_AMOUNT_ACT'])
        df_new['CONSUME'] = df_new['PN'] * (df_new['ABS_CA'] + df_new['E_AMOUNT_ACT'])
        df_new['ACT_AMT'] = df_new['PN'] * df_new['C_CONSUME']
        df_new['UNIT_AMT'] = df_new['PN'] * (df_new['ABS_AE'] + df_new['C_CONSUME'])
        df_new['FAC_AMT'] = df_new['PN'] * (df_new['ABS_CA'] + df_new['E_AMOUNT_ACT'])
        df_new['PRICE_T'] = df_new['PN'] * (df_new['ABS_CA'] + df_new['E_AMOUNT_ACT'])
        df_new['B_CONSUME'] = df_new['PN'] * (df_new['D_AMOUNT_ACT'] + df_new['E_AMOUNT_ACT'])
        df_new['B_ACT_N'] = df_new['PN'] * df_new['ABS_A'] * (df_new['D_AMOUNT_ACT'] + df_new['E_AMOUNT_ACT'])
        df_new['B_UNIT_AMT'] = df_new['PN'] * df_new['ABS_A'] * (df_new['D_AMOUNT_ACT'] + df_new['E_AMOUNT_ACT'])
        df_new['B_FAC_AMT'] = df_new['PN'] * df_new['ABS_A'] * (df_new['D_AMOUNT_ACT'] + df_new['E_AMOUNT_ACT'])
        df_new['PRICE_TB'] = df_new['PN'] * (df_new['D_AMOUNT_ACT'] + df_new['E_AMOUNT_ACT'])



        df_new.drop(['A_MAT_ACT_WT'], axis=1, inplace=True)
        df_new.drop(['C_CONSUME'], axis=1, inplace=True)
        df_new.drop(['D_AMOUNT_ACT'], axis=1, inplace=True)
        df_new.drop(['E_AMOUNT_ACT'], axis=1, inplace=True)
        df_new.drop(['ABS_AE'], axis=1, inplace=True)
        df_new.drop(['ABS_CA'], axis=1, inplace=True)
        df_new.drop(['ABS_A'], axis=1, inplace=True)
        df_new.drop(['PACK_TYPE_CODE'], axis=1, inplace=True)
        df_new.drop(['PN'], axis=1, inplace=True)
        df_new.drop(['MONTH'], axis=1, inplace=True)

        df_new.rename(columns={'ACCOUNT': 'ACCT'}, inplace=True)
        df_new.rename(columns={'FACTORY': 'DEPARTMENT_CODE'}, inplace=True)
        df_new.rename(columns={'UNIT': 'UNIT_CODE'}, inplace=True)
        df_new.rename(columns={'TEAM': 'CLASS'}, inplace=True)
        df_new.rename(columns={'WORK_TIME': 'PRODUCE_TIME'}, inplace=True)
        df_new.rename(columns={'PROCESS_START_TIME': 'PRODUCE_START_TIME'}, inplace=True)
        df_new.rename(columns={'PROCESS_END_TIME': 'PRODUCE_END_TIME'}, inplace=True)
        df_new.rename(columns={'PRODUCT_CODE': 'BYPRODUCT_CODE'}, inplace=True)
        df_new.rename(columns={'ST_NO': 'STEELNO'}, inplace=True)
        df_new.rename(columns={'WIDTH_COD': 'WIDTH_CODE'}, inplace=True)
        df_new.rename(columns={'THICK_COD': 'THICK_CODE'}, inplace=True)
        df_new.rename(columns={'MAT_NO': 'PROD_COILNO'}, inplace=True)
        df_new.rename(columns={'JHZ_KEY': 'PLAN_SYS_KV_CODE'}, inplace=True)
        df_new.rename(columns={'JHZ_KEY_NAME': 'PLAN_SYS_KV_CODE_NAME'}, inplace=True)
        df_new.rename(columns={'IN_PRODUCT_CODE': 'INPUT_BYPRODUCT_CODE'}, inplace=True)
        df_new.rename(columns={'IN_MAT_NO': 'ENTRY_COILNO'}, inplace=True)
        df_new.rename(columns={'WT': 'OUTPUT_WT'}, inplace=True)
        df_new.rename(columns={'ACT_WT': 'ACT_OUTPUT_WT'}, inplace=True)
        df_new.rename(columns={'IN_WT': 'INPUT_WT'}, inplace=True)
        df_new.rename(columns={'ACT_IN_WT': 'ACT_INPUT_WT'}, inplace=True)
        df_new.rename(columns={'WCE': 'COST_SUBJECT'}, inplace=True)
        df_new.rename(columns={'WCE_NAME': 'COST_SUBJECT_CNAME'}, inplace=True)
        df_new.rename(columns={'WCE_UNIT': 'COST_SUBJECT_UNIT'}, inplace=True)
        df_new.rename(columns={'ACT_N': 'COST_SUBJECT_ON_AMT'}, inplace=True)
        df_new.rename(columns={'CONSUME': 'UNITCONSUME'}, inplace=True)
        df_new.rename(columns={'ACT_AMT': 'PROC_ON_AMT'}, inplace=True)
        df_new.rename(columns={'UNIT_AMT': 'UNIT_PROC_ON_AMT'}, inplace=True)
        df_new.rename(columns={'FAC_AMT': 'DEPT_PROC_ON_AMT'}, inplace=True)
        df_new.rename(columns={'PRICE_T': 'TON_STEEL_PRICE'}, inplace=True)
        df_new.rename(columns={'PRICE': 'COST_SUBJECT_PRICE'}, inplace=True)
        df_new.rename(columns={'B_CONSUME': 'STD_UNITCONSUME'}, inplace=True)
        df_new.rename(columns={'B_ACT_N': 'COST_SUBJECT_STD_AMT'}, inplace=True)
        df_new.rename(columns={'B_ACT_AMT': 'PROC_STD_AMT'}, inplace=True)
        df_new.rename(columns={'B_UNIT_AMT': 'UNIT_PROC_STD_AMT'}, inplace=True)
        df_new.rename(columns={'B_FAC_AMT': 'DEPT_PROC_STD_AMT'}, inplace=True)
        df_new.rename(columns={'PRICE_TB': 'STD_TON_STEEL_PRICE'}, inplace=True)
        df_new.rename(columns={'APP_THROW_AI_MODE': 'APPTHROWAIMODE'}, inplace=True)
        df_new.rename(columns={'LAYER_TYPE': 'COATING_TYPE'}, inplace=True)
        df_new.rename(columns={'TOP_COAT_WT': 'TOP_COATING_WT'}, inplace=True)
        df_new.rename(columns={'BOT_COAT_WT': 'BOT_COATING_WT'}, inplace=True)
        df_new.rename(columns={'TYPE': 'CALC_END'}, inplace=True)
        df_new.rename(columns={'DESIGN_ANNEAL_DIAGRAM_CODE': 'ANNEAL_CURVE'}, inplace=True)
        df_new.rename(columns={'IN_MAT_WIDTH': 'ENTRY_MAT_WIDTH'}, inplace=True)
        df_new.rename(columns={'IN_WIDTH': 'ENTRY_WIDTH'}, inplace=True)
        df_new.rename(columns={'IN_WIDTH_COD': 'ENTRY_WIDTH_CODE'}, inplace=True)
        df_new.rename(columns={'IN_MAT_THICK': 'ENTRY_MAT_THICK'}, inplace=True)
        df_new.rename(columns={'IN_THICK': 'ENTRY_THICK'}, inplace=True)
        df_new.rename(columns={'IN_THICK_COD': 'ENTRY_THICK_CODE'}, inplace=True)
        df_new.rename(columns={'PLAN_NO_COD': 'PLAN_NO_CODE'}, inplace=True)
        df_new.rename(columns={'TRIM_WIDTH': 'TRIMM_WIDTH'}, inplace=True)
        df_new.rename(columns={'TRIM_WIDTH_C': 'TRIMMING_WIDTH_RANGE'}, inplace=True)
        df_new.rename(columns={'TRIM_WIDTH_COD': 'TRIMMING_WIDTH_CODE'}, inplace=True)
        df_new.rename(columns={'IN_MAT_INNER_DIA': 'ENTRY_MAT_INDIA'}, inplace=True)
        df_new.rename(columns={'LAS_NOTCH_FLAG': 'PRODUCE_NICK_FLAG'}, inplace=True)
        #df_new.rename(columns={'QS_CODE': 'PROD_CAT_CODE'}, inplace=True)
        df_new.rename(columns={'QS_NAME': 'PROD_CAT'}, inplace=True)

        XRetryableSave(p_db_conn=self.db_conn_rds, p_table_name='T_ADS_FACT_SICB_CA0001', p_schema='BGTARAS1',
                       p_dataframe=df_new,
                       p_max_times=5).redo()



        super(BZCBJSJob, self).do_execute()


